package edu.gatech.TNE.wonderLab7000.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.pool.OracleDataSource;


public class dataBaseAccesser {
	private Connection conn;
	private ResultSet result;
	private String query;
	private String[] out;
	
	public Connection connect() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:ops$mdye3/wonderLab@acme.gatech.edu:1525:public");
        conn = ods.getConnection();
        return conn;
    }
	/**
	 * Gets all labs in a course
	 * @param courseName
	 * @return
	 * @throws SQLException
	 */
	public String[] getLabs(String courseName) throws SQLException
	{
		connect();
		query = "SELECT * FROM LABS WHERE COURSE='"+courseName+"'";
    	PreparedStatement statement = conn.prepareStatement(query);
    	out = new String[100];
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            conn.close();
            statement.close();
            result.close();
			System.out.println(e.toString());
		} 
		
		int userLength = 0;
		while (result.next()) {
			userLength++;
		}
		
		out = new String[userLength];
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            conn.close();
            statement.close();
            result.close();
			System.out.println(e.toString());
		} 
		
		int i = 0;
		while (result.next()) {
			int j = 0;
			out[i] = result.getString("LABNAME");
			j++;
			
			
			i++;
		}
		
        conn.close();
        statement.close();
        result.close();
		return out;
		
	}
	/**
	 * Gets all teams in a specified lab 
	 * @param labName
	 * @return
	 * @throws SQLException
	 */
	public String[] getTeams(String course, String labName) throws SQLException
	{
		connect();
		String labID = course + "_" + labName;
		query = "SELECT * FROM "+labID;
    	PreparedStatement statement = conn.prepareStatement(query);
    	
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            conn.close();
            statement.close();
            result.close();
			System.out.println(e.toString());
		} 
		
		int userLength = 0;
		while (result.next()) {
			userLength++;
		}
		
		out = new String[userLength];
		System.out.println(userLength);
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            conn.close();
            statement.close();
            result.close();
			System.out.println(e.toString());
		} 
		
		
		int i = 0;
		while (result.next()) 
		{
			
			String str = result.getString("TEAM");
			
			if(!contains(out,str))
				out[i] = str;
		
			
			
			i++;
		}
		int newLength = 0;
		for(String str : out)
		{
			if(str != null)
				newLength++;
		}
		String[] strOut = new String[newLength];
		for(int t = 0;t<newLength;t++)
		{
			strOut[t] = out[t];
		}
        conn.close();
        statement.close();
        result.close();
		return out;
		
	}
	public boolean contains(String[] arrIn, String toBeChecked)
	{
		
		boolean contains = false;
		for(String str : arrIn)
		{
			
				if(str != null)
				{
					if(str.equals(toBeChecked))
						contains = true;
				}
			
		}
		return contains;
	}
	/**
	 * Returns all students in a specified lab and on a specified team
	 * @param teamName
	 * @param labName
	 * @return
	 * @throws SQLException
	 */
	public String[] getStudents(String teamName, String labName, String course) throws SQLException
	{
		connect();
		String labID = course + "_" + labName;
		query = "SELECT * FROM "+labID+" WHERE TEAM='" + teamName+"'";
    	PreparedStatement statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println("Sql Error "+ e.toString());
		} 
		
		int userLength = 0;
		while (result.next()) {
			userLength++;
		}
		
		out = new String[userLength];
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
		} 
		
		int i = 0;
		while (result.next()) {
			
			String str = result.getString("STUDENT");
			out[i] = str;

			i++;
		}
		
        conn.close();
        statement.close();
        result.close();
		return out;
		
	}
	
	public static void main(String[] agrs) throws SQLException
	{
		dataBaseAccesser access = new dataBaseAccesser();
		String[] strArr = access.getTeams("course", "testingSelfSelectAgain2");
		System.out.println(strArr[0]);
	}
	
	

}
